	
	**This .do file pulls detainer and removals data**
	
		

			**	**	**	**	**	**	**	**	**	**	**	**	**
			**	**	**	**	**	**	**	**	**	**	**	**	**
			**	**	**	**	**	**	**	**	**	**	**	**	**
	
*-------------------------------------------------------------------------------
* Start with full ILRC dataset and clean it
*-------------------------------------------------------------------------------

insheet using ///
"Policy Sources/1_ILRC ICE Data.csv", ///
clear

**rename variables that start with numbers
rename icecomments icecomments2015
rename v12 icecomments2017
rename detainernotificationacceptancest detaineracceptance2015

**make facility name lowercase for merge
gen detentionfacilitylower = lower(jailorprisonname)

**remove last five characters
replace detentionfacilitylower = substr(detentionfacilitylower, 1,length(detentionfacilitylower) - 5)

**remove duplicates
duplicates tag county state detentionfacilitylower, gen(dup)
count if dup > 0
assert r(N) == 0

**there are no duplicates, so drop variable created
drop dup

**create ID variable

gen ilrcid = _n

**Make state names
statastates, ab(state)

**clean up
replace state_name="GUAM" if ilrcid == 509
replace state_name="PUERTO RICO" if ilrcid == 2213

*lower case
gen state_ab=state
replace state=lower(state_name)
drop state_name
replace county=lower(county)

*drop merge variable
drop if _m==2
drop _m

*remove "county" from county names
replace county = subinstr(county," county", "", .)



*-------------------------------------------------------------------------------
* Save file for Reclink Fuzzy Matching
*-------------------------------------------------------------------------------

save "ilrc.dta", replace


*-------------------------------------------------------------------------------
* Now cut this file down to create a file to merge and find exact matches
*-------------------------------------------------------------------------------

**remove seven duplicates
sort state county detentionfacilitylower 
by state county : drop if _n > 1

**rename detention center variable since not merging on it
rename detentionfacilitylo ilrcdetentionfacility

**merge
merge 1:m county state using "detainerstates"

**keep only exact matches 
keep if _m == 3
drop _m

*remove empty observations
drop if county == ""

format %24s detentionfacilityl ilrcdetentionfa

*save
save "ilrcexactmatches", replace


*-------------------------------------------------------------------------------
* Combine Exact Matches and Manual Matches
*-------------------------------------------------------------------------------


append using "manualmatches"






			**	**	**	**	**	**	**	**	**	**	**	**	**
			**	**	**	**	**	**	**	**	**	**	**	**	**
			**	**	**	**	**	**	**	**	**	**	**	**	**

*-------------------------------------------------------------------------------
* deal with detention center duplicates
*-------------------------------------------------------------------------------

**
*We can drop those duplicates that have all the same polices and keep their detention centers
**

duplicates drop detentionfacilitycode detaineracceptance2015 icecomments2015 icecomments2017 ///
noiceholds , force

*left with 4,600 unique observations
count
assert r(N) == 4610


			**	**	**	**	**	**	**	**	**	**	**	**	**
			**	**	**	**	**	**	**	**	**	**	**	**	**
			**	**	**	**	**	**	**	**	**	**	**	**	**

*-------------------------------------------------------------------------------
* merge with full ice detainers dataset
*-------------------------------------------------------------------------------

*drop excess ICE variables before merge
drop areaofresponsibility site detentionfacility

**merge
merge 1:m detentionfacilitycode using ///
"detainersallyears.dta"

**this produces about 4,000 nonmatches
count if _m == 2 
assert r(N) == 3435

*many more county nonmatches bc of state facilities, fed facilities, and some manual nonmatches
count if county == ""
assert r(N) == 509024

**compress
compress

**read date
gen temp = date(prep, "MDY", 2030)
format temp %td
drop preparedate
rename temp preparedate



*-------------------------------------------------------------------------------
* collapse to county month level for merge with removals data
*-------------------------------------------------------------------------------


**
*Remove one errant county name for state facility
**

replace county = "" if county == "dade" & statefac == 1


***
*Create month variable
***

gen prepmonth = mofd(preparedate)
format prepmonth %tm

***
*Create year variable
***

gen prepyear = year(preparedate)

***
*Create facility string variable
***

gen facility = "state" if statefac == 1
replace facility = "federal" if fedfac == 1
replace facility = "local" if facility != "federal" & facility !="state"


**
*Drop multi-county detention centers
**

drop if county2 != ""

**
*Drop federal facilities
**

drop if fedfac == 1


* Generate counts for collapse
*--------------------------------------

*count of all detainers issued
gen detainercount = 1 

*count of all resulting in bookins
gen bookincount = 1 if detainerliftreason == "Booked into Detention" 

*count of all notification requests
gen notifyreqcount = 1 if notifyreleaserequestyesno == "YES"

*count of all declined detainers
gen declinecount = 1 if detainerliftreason == "Detainer Declined by LEA" 


* Remove county names where facility is a state one
*--------------------------------------
replace county = "" if facility == "state"


*-------------------------------------------------------------------------------
* Collapse
*-------------------------------------------------------------------------------
collapse (sum) detainercount-declinecount, by(state county prepmonth)


rename prepmonth month


* Merge in large county data
*--------------------------------------
replace county = "prince george's" if county == "prince george"
replace county = "virginia beach city" if county == "virginia beach"
replace county = "anchorage municipality" if county == "anchorage"
replace county = "chaves" if county == "chavez" & state == "new mexico"
replace county = "northampton" if county == "northhampton" & state == "pennsylvania"
replace county = "district of columbia" if state == "district of columbia" & county == ""

*Make New York a single county

replace county = "new york" if county == "bronx" & state == "new york"
replace county = "new york" if county == "kings" & state == "new york"
replace county = "new york" if county == "richmond" & state == "new york"
replace county = "new york" if county == "queens" & state == "new york"

collapse (sum) detainercount-declinecount, by(state county month)

merge 1:1 state county month using "removalcounts"

**check for counties where no months match
bysort state county: egen avmerge = mean(_m)
bysort state county: egen maxmerge = max(_m)

**two counties missing from detainers data
assert county == "new castle" | county == "new london" if avmerge == 2 & maxmerge < 3

*drop these

drop if avmerge == 2 & maxmerge < 3
drop avmerge

**now keep just states and counties that match
drop if _m == 1


**Replace missing values with zeros for counts
foreach var of varlist detainercount-declinecount{
replace `var' = 0 if `var' == . & month <= m(2017m6)
}

**now take a look at deportation counts when detainer counts are zero
su countymonthly if detainercount == 0 , detail

**what's happening in the ones with lots of deportations--not many and mostly border counties
tab state county if detainercount == 0 & countymonthlytotal > 50 & countymonthlytotal < .

**find first detainer month for each county
bysort statecounty: egen temp = min(month) if detainercount > 0
bysort statecounty: egen firstdetmonth  = mean(temp)
drop temp


*-------------------------------------------------------------------------------
* save
*-------------------------------------------------------------------------------

save "detainerremovalcounts", replace




















